﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F5AssemblyWTS : UserControl
    {

        private INISettings mySetting;
        private SqlConnection myConnection;

        public panel_F5AssemblyWTS()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.viewMode.SelectedIndex = 0;
            this.F5WTS.PopupMenuShowing += F5WTS_PopupMenuShowing;
            this.f5AssemblyWTSContextMenu.ItemClicked += f5AssemblyWTSContextMenu_ItemClicked;
        }

        void f5AssemblyWTSContextMenu_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.f5AssemblyWTSContextMenu.Hide();            
            switch (e.ClickedItem.Name)
            {
                case "editSelection":
                    buttonEdit_Click(sender, e);
                    break;
                case "addSelection":
                    buttonAdd_Click(sender, e);
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void F5WTS_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.f5AssemblyWTSContextMenu.Show(MousePosition);
        }        
        
        ///////////////////////////////////////////
        /// Load Data From 231_F5TimeInput
        //////////////////////////////////////////
        private void LoadData()
        {
            gridF5WTS.DataSource = null;
            F5WTS.Columns.Clear();
            DataSet sourceDataSet = new DataSet();
            string sql = "SELECT [231_TimeInput].ID, [231_TimeInput].Date, [231_TimeInput].StaffID, [222_Staff].StaffName, [231_TimeInput].TimePlanNo,"
                + " [231_TimeInput].TimeWorkID, [231_TimeWork].TimeWorkName, [231_TimeInput].CompType, [231_TimeInput].AssemblyTime,"
                + " [231_TimeInput].ActualQuantity, [231_TimeInput].Note"
                + " FROM [231_TimeInput] LEFT OUTER JOIN"
                + " [222_Staff] ON [231_TimeInput].StaffID=[222_Staff].StaffID LEFT OUTER JOIN"
                + " [231_TimeWork] ON [231_TimeInput].TimeWorkID=[231_TimeWork].TimeWorkID"
                + " WHERE (DATEDIFF(day, [231_TimeInput].Date, GETDATE()) < 30) AND ([231_TimeInput].F5 = 1)"
                + " ORDER BY [231_TimeInput].ID DESC";
            myConnection.Open();
            SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd_sql;            
            adapter.Fill(sourceDataSet);
            adapter.Dispose();
            cmd_sql.Dispose();
            myConnection.Close();
            if (sourceDataSet != null)
            {
                gridF5WTS.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("Date", typeof(DateTime));
                newTable.Columns.Add("StaffID", typeof(string));
                newTable.Columns.Add("StaffName", typeof(string));
                newTable.Columns.Add("TimePlanNo", typeof(string));
                newTable.Columns.Add("TimeWorkID", typeof(string));
                newTable.Columns.Add("TimeWorkName", typeof(string));
                newTable.Columns.Add("CompType", typeof(string));
                newTable.Columns.Add("AssemblyTime", typeof(int));
                newTable.Columns.Add("ActualQuantity", typeof(int));
                newTable.Columns.Add("Note", typeof(string));                
                gridF5WTS.DataSource = newTable;
            }
            F5WTS.Columns[0].Caption = "ID";
            F5WTS.Columns[1].Caption = "Date";
            F5WTS.Columns[2].Caption = "Code";
            F5WTS.Columns[3].Caption = "Person";
            F5WTS.Columns[4].Caption = "Order No";
            F5WTS.Columns[5].Caption = "Time Work ID";
            F5WTS.Columns[6].Caption = "Time Work Name";
            F5WTS.Columns[7].Caption = "Comp Type";            
            F5WTS.Columns[8].Caption = "F5 Time";
            F5WTS.Columns[9].Caption = "Number";            
            F5WTS.Columns[10].Caption = "Remark";
            F5WTS.Columns[0].Visible = false;
            F5WTS.BestFitColumns();
        }

        ///////////////////////////////////////////////////
        /// View Filter Data
        ///////////////////////////////////////////////////
        private void buttonView_Click(object sender, EventArgs e)
        {
            string sql = null;
            string error = null;
            
            gridF5WTS.DataSource = null;
            F5WTS.Columns.Clear();

            if (this.fromDate.EditValue == null)
            {
                error = "Please select From date";
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.toDate.EditValue == null)
                {
                    error = "Please select To date";
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                switch (this.viewMode.SelectedIndex)
                {
                    case 0:
                        sql = "SELECT [231_TimeInput].Date, [222_Staff].StaffID AS Code, [222_Staff].StaffName,"
                            + " [231_TimeInput].TimePlanNo, [231_TimeInput].TimeWorkID, [231_TimeWork].TimeWorkName,"
                            + " [231_TimeInput].CompType, [231_TimeInput].AssemblyTime, [231_TimeInput].Note"
                            + " FROM [231_TimeInput] LEFT OUTER JOIN"
                            + " [222_Staff] ON [231_TimeInput].StaffID=[222_Staff].StaffID LEFT OUTER JOIN"
                            + " [231_TimeWork] ON [231_TimeInput].TimeWorkID=[231_TimeWork].TimeWorkID"
                            + " WHERE ([231_TimeInput].Date BETWEEN @FromDate AND @ToDate) AND ([231_TimeInput].F5=1)"
                            + " ORDER BY [231_TimeInput].Date DESC";
                        break;
                    case 1:
                        sql = "SELECT [231_TimeInput].Date, [222_Staff].StaffID AS Code, [222_Staff].StaffName,"
                            + " SUM([231_TimeInput].AssemblyTime) AS TotalTime"
                            + " FROM [231_TimeInput] INNER JOIN"
                            + " [222_Staff] ON [231_TimeInput].StaffID = [222_Staff].StaffID"
                            + " WHERE ([231_TimeInput].F5=1)"
                            + " GROUP BY [231_TimeInput].Date, [222_Staff].StaffID, [222_Staff].StaffName"
                            + " HAVING ([231_TimeInput].Date BETWEEN @FromDate AND @ToDate) "
                            + " ORDER BY [231_TimeInput].Date DESC";
                        break;
                    case 2:
                        sql = "SELECT [231_TimeInput].ID, [231_TimeInput].TimePlanNo, [231_F5Plan].PartNo,"
                            + " [231_F5Plan].CommandQuantity, [222_Staff].StaffName AS Code, [222_Staff].StaffName,"
                            + " [231_TimeInput].TimeWorkID, [231_TimeWork].TimeWorkName, [231_TimeInput].CompType,"
                            + " [231_TimeInput].Date, [231_F5Plan].FinishedDate"
                            + " FROM [231_F5Plan] INNER JOIN"
                            + " [231_TimeInput] ON [231_F5Plan].OrderNo = [231_TimeInput].TimePlanNo"
                            + " AND [231_F5Plan].FinishedDate < [231_TimeInput].Date INNER JOIN"
                            + " [222_Staff] ON [231_TimeInput].StaffID = [222_Staff].StaffID LEFT OUTER JOIN"
                            + " [231_TimeWork] ON [231_TimeInput].TimeWorkID=[231_TimeWork].TimeWorkID"
                            + " WHERE ([231_TimeInput].Date BETWEEN @FromDate AND @ToDate)"
                            + " AND ([231_TimeInput].TimePlanNo NOT LIKE N'Z%') AND ([231_TimeInput].F5=1)";
                        break;
                }

                if (string.IsNullOrEmpty(sql))
                {
                    MessageBox.Show("Not found!");
                }
                else
                {
                    try
                    {
                        DataSet sourceDataSet = new DataSet();
                        myConnection.Open();
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd_sql;
                        cmd_sql.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = DateTime.Parse(this.fromDate.EditValue.ToString());
                        cmd_sql.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = DateTime.Parse(this.toDate.EditValue.ToString());
                        adapter.Fill(sourceDataSet);
                        adapter.Dispose();
                        cmd_sql.Dispose();
                        myConnection.Close();

                        if (sourceDataSet != null)
                        {
                            if (sourceDataSet.Tables[0].Rows.Count > 0)
                            {                                
                                gridF5WTS.DataSource = sourceDataSet.Tables[0];
                                //F5WTS.Columns[0].Visible = false;
                                //MessageBox.Show("Found " + sourceDataSet.Tables[0].Rows.Count + " results");
                                sourceDataSet.Tables.Clear();
                            }
                            else
                            {
                                if (this.viewMode.SelectedIndex == 2)
                                {
                                    gridF5WTS.DataSource = null;
                                    F5WTS.Columns.Clear();
                                    sourceDataSet.Tables.Clear();

                                    sql = "SELECT [231_TimeInput].Date, [222_Staff].StaffID AS Code, [222_Staff].StaffName,"
                                        + " [231_TimeInput].TimeWorkID, [231_TimeWork].TimeWorkName, [231_TimeInput].TimePlanNo,"
                                        + " [231_TimeInput].AssemblyTime as [Working hours]"
                                        + " FROM [231_TimeInput] LEFT OUTER JOIN"
                                        + " [222_Staff] ON [222_Staff].StaffID=[231_TimeInput].StaffID LEFT OUTER JOIN"
                                        + " [231_TimeWork] ON [231_TimeInput].TimeWorkID=[231_TimeWork].TimeWorkID"
                                        + " WHERE ([231_TimeInput].Date BETWEEN @FromDate AND @ToDate) AND ([231_TimeInput].F5=1)"
                                        + " ORDER BY [231_TimeInput].Date, [222_Staff].StaffID";
                                    myConnection.Open();
                                    cmd_sql = new SqlCommand(sql, myConnection);
                                    adapter = new SqlDataAdapter();
                                    adapter.SelectCommand = cmd_sql;
                                    cmd_sql.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = DateTime.Parse(this.fromDate.EditValue.ToString());
                                    cmd_sql.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = DateTime.Parse(this.toDate.EditValue.ToString());
                                    adapter.Fill(sourceDataSet);
                                    adapter.Dispose();
                                    cmd_sql.Dispose();
                                    myConnection.Close();
                                    if (sourceDataSet.Tables[0].Rows.Count > 0)
                                    {                                        
                                        gridF5WTS.DataSource = sourceDataSet.Tables[0];
                                        //F5WTS.Columns[0].Visible = false;
                                        //MessageBox.Show("Found " + sourceDataSet.Tables[0].Rows.Count + " results");
                                        sourceDataSet.Tables.Clear();
                                    }
                                    else
                                    {
                                        MessageBox.Show("Not Found!");
                                    }
                                }
                                else
                                {
                                    MessageBox.Show("Not Found!");
                                }
                            }
                        }
                        else
                        {
                            MessageBox.Show("Not Found!");
                        }
                        F5WTS.BestFitColumns();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //MessageBox.Show("Not Found!");
                    }
                }
            }
            else
            {
                MessageBox.Show(error);
            }          
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_F5AssemblyWTS add_form = new frm_Add_F5AssemblyWTS();
            add_form.AddEdit = "Add";
            add_form.ShowDialog();
            LoadData();
        }

        private void buttonEdit_Click(object sender, EventArgs e)
        {
            frm_Add_F5AssemblyWTS edit_form = new frm_Add_F5AssemblyWTS();
            edit_form.AddEdit = "Edit";
            edit_form.ShowDialog();
            LoadData();
        }

        private void panel_F5AssemblyWTS_Load(object sender, EventArgs e)
        {
            LoadData();
        }

        private void buttonExport_Click(object sender, EventArgs e)
        {
            Cursor currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                SaveFileDialog saveFDlg = new SaveFileDialog();
                saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                saveFDlg.Title = "Save As";
                saveFDlg.FileName = "F5AssemblyWTS.xlsx";
                if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                {
                    if (!string.IsNullOrEmpty(saveFDlg.FileName))
                    {
                        F5WTS.OptionsPrint.PrintDetails = true;
                        F5WTS.OptionsPrint.ExpandAllDetails = true;
                        F5WTS.OptionsPrint.AutoWidth = false;
                        Font default_font = new Font("Times New Roman", 10);
                        F5WTS.AppearancePrint.Row.Font = default_font;
                        F5WTS.AppearancePrint.HeaderPanel.Font = default_font;
                        F5WTS.OptionsPrint.UsePrintStyles = true;
                        //RV201F06.ExportToXlsx(saveFDlg.FileName);
                        DevExpress.XtraPrinting.XlsxExportOptions myOptions = new DevExpress.XtraPrinting.XlsxExportOptions();
                        //myOptions.SheetName = "Data";
                        myOptions.TextExportMode = DevExpress.XtraPrinting.TextExportMode.Text;
                        myOptions.ShowGridLines = true;
                        F5WTS.ExportToXlsx(saveFDlg.FileName, myOptions);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Warning!");
            }
            finally
            {
                Cursor.Current = currentCursor;
            }
        }

        private void deleteRecord()
        {
            int[] rows = F5WTS.GetSelectedRows();
            if (rows.Length <= 0)
            {
                MessageBox.Show("There is no selected record to delete");
            }
            else
            {
                if (MessageBox.Show("Are you really to delete selected records now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    string[] id_list = new string[rows.Length];
                    for (int i = 0; i < rows.Length; i++)
                    {
                        DataRow selected_row = F5WTS.GetDataRow(rows[i]);
                        id_list[i] = "'" + selected_row[0].ToString() + "'";
                    }
                    string id = string.Join(",", id_list);

                    try
                    {
                        myConnection.Open();
                        string sql = "DELETE [231_TimeInput] WHERE ID IN (" + id + ")";
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();
                        myConnection.Close();
                        MessageBox.Show("F5 WTS deleted success.");
                        LoadData();
                    }
                    catch //(Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        //MessageBox.Show(ex.Message);
                        MessageBox.Show("Cannot delete F5 WTS. Please try again.");
                    }
                }
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteRecord();
        }
    }
}
